package com.handu.luna.core.domain.goods;

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import com.handu.luna.core.domain.BaseRepository;

public interface CategoryRepository extends JpaRepository<Category,Long>, BaseRepository<Category>,JpaSpecificationExecutor<Category>{
	
	@Query("from Category c where displayed = true  ")
	List<Category> getDisplayList();
 
	@Query("from Category c where c.parentId = :parentId order by displayOrder asc")
	List<Category> getParent(@Param("parentId") Long parentId);
	
	@Query("select c.id from Category c where c.parentId = :parentId and c.displayed = true")
	List<Long> getChildId(@Param("parentId") Long parentId);
	
	@Query("from Category c where c.parentId = 0 and displayed = true order by displayOrder asc")
	List<Category> getParent4Portal();
	
	@Query("delete from Category c   where c.parentId =  :parentId  ")
	List<Category> deleteParent(@Param("parentId") Long parentId);
	
	@Query("select c from Category c   where c.parentId =  :parentId and c.name =:name ")
	List<Category> findByNameAndPid(@Param("name") String name,@Param("parentId") Long parentId);
	
	/**
	 * 判断该分类下有没有子分类
	 * @param id
	 * @return
	 */
	@Query("from Category c where c.parentId= :id")
	List<Category> getAllByCatId(@Param("id") long id);
	
	@Query("from Category c where c.attributeIds like CONCAT('%',:attributeIds,'%')")
	Category findByAttributeId(@Param("attributeIds") String attributeIds);
	
	/**
	 * 判断该分类下有没有子分类   新的方法      
	 * @param id
	 * @return
	 */   
	//SELECT *,CONCAT(parentPath,',',id) as orderby FROM `cat` WHERE CONCAT(',',parentPath,',') like ',1,%' ORDER BY CONCAT(parentPath,',',id) ASC
	@Query("from Category c  where CONCAT(',',c.parentCollection,',')  like CONCAT('%,',:parentCollection,',%') ORDER BY CONCAT(c.parentCollection,',',parentCollection) asc")
	List<Category> getAllByCatIdNew(@Param("parentCollection") String parentCollection);   //order by id asc

	
	@Query("SELECT id from Category c  where CONCAT(',',c.parentCollection,',')  like CONCAT('%,',:parentCollection,',%')")
	List<Long> getAllCatIdByParentCollection(@Param("parentCollection") String parentCollection); 
	
	
	/**
	 * 根据给出的ID获取ID对应的分类以及所有下属分类
	 * @param parentCollection
	 * @return
	 */
	@Query("SELECT id from Category c  where CONCAT(',',c.parentCollection,',')  like CONCAT('%,',:parentCollection,',%') OR id = :parentCollection")
	List<Long> getAllAndSelfCatIdByParentCollection(@Param("parentCollection") String parentCollection);
	
	
	/**
	 * 根据给出的ID获取ID对应的分类以及所有下属分类
	 * @param parentCollection
	 * @return
	 */
	@Query("SELECT id from Category c  where CONCAT(',',c.parentCollection,',')  like CONCAT('%,',:parentCollection,',%') OR id = :parentCollection  and c.displayed = true")
	List<Long> getAllDisplayedCatIdByParentCollection(@Param("parentCollection") String parentCollection);
	
	/**
	 * 获取分类下的直接子分类
	 * @param parentCollection
	 * @return
	 */
	@Query("SELECT c from Category c  where c.parentCollection  = :parentCollection and c.displayed = true")
	List<Category> getSelfCatByParentCollection(@Param("parentCollection") String parentCollection);
	
	/**
	 * 获取顶级分类
	 * @return
	 */
	@Query("SELECT c from Category c  where (c.parentCollection IS NULL OR c.parentCollection = '') and c.displayed = true")
	List<Category> getTopCategory();
	
	/**
	 * 前端分类展示 lxx
	 */
	//@Cacheable("from Category c ORDER BY CONCAT(c.parentCollection) asc")
	//List<Category> getAllCategory();


	
}
